/*******************************************************************************
 * Copyright (c) 2022-09-01 @author <a href="mailto:iffiff1@gmail.com">Tyler Chen</a>.
 * All rights reserved.
 *
 * Contributors:
 *     <a href="mailto:iffiff1@gmail.com">Tyler Chen</a> - initial API and implementation.
 ******************************************************************************/
package org.iff.util;

import org.apache.commons.io.FileUtils;
import org.apache.commons.io.IOUtils;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateUtils;

import java.io.*;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.concurrent.atomic.AtomicBoolean;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.Collectors;

/**
 * XmlXlsxParser
 *
 * @author <a href="mailto:iffiff1@gmail.com">Tyler Chen</a>
 * @since 2022-09-01
 */
public class XmlXlsxParser implements Closeable {
    private final static Map<String, String> escapeChars = new HashMap<>();
    private final AtomicInteger id = new AtomicInteger(1);
    /**
     * [main,worksheet,theme,styles,sharedStrings,customXmlProperties]
     */
    private Map<String, List<String>> contentTypes = new HashMap<>();
    /**
     * main: workbook.xml
     */
    private XmlLinkedMap workbook = new XmlLinkedMap();
    private List<XmlLinkedMap> sheets = new ArrayList<>();
    /**
     * xl/_rels/workbook.xml.rels
     */
    private XmlLinkedMap workbookRels = new XmlLinkedMap();
    /**
     * xl/styles.xml
     */
    private XmlLinkedMap workbookStyle = new XmlLinkedMap();
    private List<String> sharedStrings = new ArrayList<>();
    private Map<String, String> relIdTargetMap = new HashMap<>();
    private Map<Integer, XmlLinkedMap> styleNodeIdMap = new HashMap<>();
    private Map<String, String> styleIdNameMap = new HashMap<>();
    private Map<String, String> styleIdBaseOnMap = new HashMap<>();
    private String defaultParagraphFont = "";
    private File baseDir = null;
    private File unzipDir = null;
    private SnowflakeId snowflakeId = null;
    private String fileId = null;

    public XmlXlsxParser(SnowflakeId snowflakeId) {
        this.snowflakeId = snowflakeId;
        initDir();
    }

    public XmlXlsxParser(SnowflakeId snowflakeId, File baseDir, String fileId) {
        this.snowflakeId = snowflakeId;
        this.baseDir = baseDir;
        this.fileId = fileId;
        initDir();
    }

    void initDir() {
        if (fileId == null) {
            fileId = UUID.randomUUID().toString().replace("-", "");
        }
        if (baseDir == null) {
            baseDir = new File(FileUtils.getTempDirectory(), "unzip");
        }
        unzipDir = new File(baseDir, fileId);
        if (unzipDir.exists()) {
            FileUtils.deleteQuietly(unzipDir);
            unzipDir = new File(baseDir, fileId);
        }
        unzipDir.mkdirs();
    }

    public void parse(File file) {
        FileInputStream fis = null;
        try {
            fis = new FileInputStream(file);
            parse(fis);
        } catch (Exception e) {
            throw new RuntimeException("parse doc error.", e);
        } finally {
            IOUtils.closeQuietly(fis);
        }
    }

    public void parse(InputStream docInputStream) {
        try {
            {//unzip to tmp
                ZipHelper.unzip(docInputStream, unzipDir);
            }
            {//解释 XML 文档位置
                parseContentTypes(new ByteArrayInputStream(FileUtils.readFileToByteArray(new File(unzipDir, "/[Content_Types].xml"))));
            }
            String styles = Utils.safeGet(contentTypes.get("styles"), 0);
            {//解释样式表
                parseXml(workbookStyle, new ByteArrayInputStream(FileUtils.readFileToByteArray(new File(unzipDir, styles))));
                initBuildInStyles();
            }
            {//解释文档引用
                parseXml(workbookRels, new ByteArrayInputStream(FileUtils.readFileToByteArray(new File(unzipDir, "xl/_rels/workbook.xml.rels"))));
            }
            {//解释共享字符串sharedStrings
                String sharedStringsFile = Utils.safeGet(contentTypes.get("sharedStrings"), 0);
                XmlLinkedMap sharedStringMap = new XmlLinkedMap();
                if (Utils.isNotNullOrBlank(sharedStringsFile)) {
                    parseXml(sharedStringMap, new ByteArrayInputStream(FileUtils.readFileToByteArray(new File(unzipDir, sharedStringsFile))));
                }
                List<XmlLinkedMap> siList = sharedStringMap.filter(c -> c.isTag("si"), new AtomicBoolean(false));
                for (XmlLinkedMap si : siList) {
                    List<XmlLinkedMap> t = si.filter(c -> c.isTag("t"), new AtomicBoolean(false));
                    String text = t.stream().map(txt -> txt.value()).collect(Collectors.joining(""));
                    sharedStrings.add(unEscape(text));
                }
            }
            String main = Utils.safeGet(contentTypes.get("main"), 0);
            System.out.println("Document:" + main);
            {//解释文档主体内容 main: workbook.xml
                parseXml(workbook, new ByteArrayInputStream(FileUtils.readFileToByteArray(new File(unzipDir, main))));
            }
            {//解释所有的 sheet
                List<XmlLinkedMap> sheetList = workbook.filter(c -> c.isTag("sheet"), new AtomicBoolean(false));
                for (XmlLinkedMap sheet : sheetList) {
                    String name = sheet.attr("name");
                    String sheetId = sheet.attr("sheetId");
                    String rid = sheet.attr("r:id");
                    XmlLinkedMap sheetRels = workbookRels.filterFirst(c -> c.isTag("Relationship") && rid.equals(c.attr("Id")));
                    if (sheetRels == null) {
                        continue;
                    }
                    String target = sheetRels.attr("Target");
                    XmlLinkedMap sheetMap = parseXml(new XmlLinkedMap(), new ByteArrayInputStream(FileUtils.readFileToByteArray(new File(unzipDir, "xl/" + target))));
                    sheetMap.attr("name", name).attr("sheetId", sheetId).attr("rid", rid);
                    sheets.add(sheetMap);
                }
            }
        } catch (Exception e) {
            throw new RuntimeException("parse doc file error", e);
        }
    }

    public List<XmlLinkedMap> getSheets() {
        return sheets;
    }

    /**
     * @return {sheetName: {html,css}}
     */
    public XmlLinkedMap sheetToHtml(Map<String, List<CharSequence>> searchContent) {
        XmlLinkedMap sheetHtml = new XmlLinkedMap();
        for (XmlLinkedMap sheet : sheets) {
            XmlLinkedMap htmlMap = new XmlLinkedMap();
            String sheetName = sheet.attr("name");
            sheetHtml.attr(sheetName, htmlMap);
            List<CharSequence> contents = new ArrayList<>();
            contents.add("<table class=\"excel-table\">");
            ExcelRange excelRange = excelRange(sheet);
            int startCellIndex = excelRange.start.cellNum;
            int endCellIndex = excelRange.end.cellNum;
            int cellCount = endCellIndex - startCellIndex + 1;
            {//add table header
                contents.add("<thead>");
                for (int i = 0; i < cellCount; i++) {
                    contents.add("<th class=\"excel-table-th\">" + ExcelNum.radix(i + 1) + "</th>");
                }
                contents.add("</thead>");
            }
            contents.add("<tbody>");
            {
                List<XmlLinkedMap> rows = sheet.filterFirst(c -> c.isTag("sheetData")).filter(c -> c.isTag("row"), new AtomicBoolean(false));
                rows = Utils.nullToEmptyList(rows);
                rows.forEach(r -> r.attr("r", Integer.valueOf(r.attr("r"))));
                rows = rows.stream().sorted(Comparator.comparing(a -> (int) a.attr("r"))).collect(Collectors.toList());
                int rowIndex = 1;
                for (XmlLinkedMap row : rows) {
                    int currentRow = row.attr("r");
                    //fill empty row
                    for (int emptyRow = 1; emptyRow < currentRow - rowIndex; emptyRow++) {
                        contents.add("<tr>");
                        contents.add(StringUtils.repeat("<td class=\"excel-table-td-empty\"></td>", cellCount));
                        contents.add("</tr>");
                    }
                    rowIndex = currentRow;
                    //END: fill empty row
                    contents.add("<tr>");
                    for (int i = 0; i < cellCount; i++) {
                        String cellName = ExcelNum.radix(excelRange.start.cellNum + i) + currentRow;
                        XmlLinkedMap cell = row.filterFirst(c -> cellName.equals(c.attr("r")));
                        if (cell == null) {
                            contents.add("<td class=\"excel-table-td-empty\"></td>");
                        } else {
                            XmlLinkedMap valueCell = cell.filterFirst(c -> c.isTag("v"));
                            if (valueCell != null) {
                                String value = valueCell.value();
                                if ("s".equals(cell.attr("t"))) {//shareString
                                    Integer ssIndex = Integer.valueOf(value);
                                    String shareString = Utils.safeGet(sharedStrings, ssIndex);
                                    contents.add("<td class=\"excel-table-td-s\">" + StringUtils.replace(StringUtils.defaultString(shareString, ""), "\n", "<br/>") + "</td>");
                                    if (searchContent != null) {
                                        List<CharSequence> list = searchContent.get(sheetName);
                                        if (list == null) {
                                            searchContent.put(sheetName, list = new ArrayList<>());
                                        }
                                        list.add(shareString);
                                    }
                                } else {
                                    String formatCode = getNumFmt(cell.attr("s"));
                                    String formatValue = formatCell(value, formatCode);
                                    contents.add("<td class=\"excel-table-td-fmt\">" + formatValue + "</td>");
                                    if (searchContent != null) {
                                        List<CharSequence> list = searchContent.get(sheetName);
                                        if (list == null) {
                                            searchContent.put(sheetName, list = new ArrayList<>());
                                        }
                                        list.add(formatValue);
                                    }
                                }
                            } else {
                                contents.add("<td class=\"excel-table-td-empty\"></td>");
                            }
                        }
                    }
                    contents.add("</tr>");
                }
            }
            contents.add("</tbody>");
            contents.add("</table>");
            htmlMap.attr("html", StringUtils.join(contents, "\n"));
        }
        return sheetHtml;
    }

    public String getNumFmt(String cellStyle) {
        XmlLinkedMap xmlMap = workbookStyle.filterFirst(c -> c.isTag("cellXfs"));
        if (xmlMap == null) {
            return "";
        }
        Integer index = Utils.toInt(cellStyle);
        if (index == null) {
            return "";
        }
        XmlLinkedMap xfStyle = Utils.safeGet(xmlMap.children(), index);
        if (xfStyle == null) {
            return "";
        }
        String numFmtId = xfStyle.attr("numFmtId");
        if (numFmtId == null) {
            return "";
        }
        XmlLinkedMap style = workbookStyle.filterFirst(c -> c.isTag("numFmt") && numFmtId.equals(c.attr("numFmtId")));
        if (style == null) {
            return "";
        }
        return style.attr("formatCode");
    }

    public ExcelRange excelRange(XmlLinkedMap sheet) {
        String minCell = "", maxCell = "";
        int minCellIndex = Integer.MAX_VALUE, maxCellIndex = 0;
        List<XmlLinkedMap> cells = sheet.filter(c -> c.isTag("c") && c.children() != null && c.children().size() > 0, new AtomicBoolean(false));
        for (XmlLinkedMap cell : cells) {
            String cellName = cell.attr("r");
            int cellIndex = ExcelNum.cellIndex(cellName);
            if (minCellIndex > cellIndex) {
                minCellIndex = cellIndex;
                minCell = cellName;
            }
            if (maxCellIndex < cellIndex) {
                maxCellIndex = cellIndex;
                maxCell = cellName;
            }
        }
        return new ExcelRange(new ExcelNum(minCell), new ExcelNum(maxCell));
    }

    public void parseContentTypes(InputStream is) {
        try {
            XmlLinkedMap root = new XmlParser(is).parse();
            for (XmlLinkedMap child : root.children()) {
                if (child.tag().isEmpty()) {
                    continue;
                }
                if (child.hasAttr("PartName")) {
                    String partName = (String) child.attr("PartName");
                    String contentType = (String) child.get("ContentType");
                    int start = contentType.lastIndexOf('.');
                    int end = contentType.lastIndexOf('+');
                    if (start > 0 && end > 0) {
                        String key = contentType.substring(start + 1, end);
                        List<String> parts = contentTypes.get(key);
                        if (parts == null) {
                            contentTypes.put(key, parts = new ArrayList<>());
                        }
                        parts.add(partName);
                    }
                }
            }
        } catch (Exception e) {
            throw new RuntimeException("parseContentTypes error.", e);
        }
    }

    XmlLinkedMap parseXml(XmlLinkedMap root, InputStream is) {
        try {
            XmlLinkedMap parse = new XmlParser(is).parse();
            root.putAll(parse);
            return root;
        } catch (Exception e) {
            throw new RuntimeException("parse xml error:", e);
        }
    }

    public List<XmlLinkedMap> toc() {
        List<XmlLinkedMap> result = new ArrayList<>();
        for (XmlLinkedMap sheet : sheets) {
            String sheetName = sheet.attr("name");
            result.add(new XmlLinkedMap().id(id.getAndIncrement()).attr("title", sheetName).attr("hyperLink", tocBookmark(sheetName)));
        }
        return result;
    }

    String tocBookmark(String sheetName) {
        return sheetName.length() + "000" + Math.abs(sheetName.hashCode());
    }

    /**
     * @return [{html, text, bookmarks}]
     */
    public List<XmlLinkedMap> outputHtmlByTocBookMark() {
        List<XmlLinkedMap> htmlFragment = new ArrayList<>();
        Map<String, List<CharSequence>> searchContent = new LinkedHashMap();
        XmlLinkedMap xmlMap = sheetToHtml(searchContent);
        for (String sheetName : xmlMap.keySet()) {
            XmlLinkedMap html = xmlMap.attr(sheetName);
            String htmlContent = StringUtils.defaultString(html.attr("html"), "");
            List<CharSequence> charSequences = searchContent.get(sheetName);
            htmlFragment.add(new XmlLinkedMap()
                    .attr("html", htmlContent)
                    .attr("text", StringUtils.join(charSequences, " "))
                    .attr("bookmarks", Utils.toList(tocBookmark(sheetName))));
        }
        return htmlFragment;
    }

    /**
     * 删除解压的文件
     */
    @Override
    public void close() {
        FileUtils.deleteQuietly(unzipDir);
    }

    public List<String> getSharedStrings() {
        return sharedStrings;
    }

    void initBuildInStyles() {
        XmlLinkedMap numFmts = workbookStyle.filterFirst(c -> c.isTag("numFmts"));
        if (numFmts == null) {
            List<XmlLinkedMap> list = workbookStyle.children();
            if (list == null) {
                workbookStyle.children(list = new ArrayList<>());
            }
            list.add(numFmts = new XmlLinkedMap());
        }
        //<numFmt numFmtId="176" formatCode="m&quot;月&quot;d&quot;日&quot;;@" />
        List<XmlLinkedMap> list = numFmts.children();
        if (list == null) {
            numFmts.children(list = new ArrayList());
        }
        for (int i = 0; i < buildInNumFmt.length; i++) {
            String fmt = buildInNumFmt[i];
            if (StringUtils.isBlank(fmt)) {
                continue;
            }
            list.add(new XmlLinkedMap()
                    .tag("numFmt")
                    .attr("numFmtId", String.valueOf(i))
                    .attr("formatCode", StringUtils.replace(fmt, "\"", "&quot;"))
            );
        }
    }

    public static String unEscape(String text) {
        if (text == null) {
            return text;
        }
        if (escapeChars.isEmpty()) {
            for (int i = 0; i < 256; i++) {
                escapeChars.put("&#" + i + ";", String.valueOf((char) i));
                escapeChars.put("&#" + StringUtils.leftPad(String.valueOf(i), 3, '0') + ";", String.valueOf((char) i));
            }
            escapeChars.put("&quot;", "\"");
            escapeChars.put("&apos;", "'");
            escapeChars.put("&amp;", "&");
            escapeChars.put("&lt;", "<");
            escapeChars.put("&gt;", ">");
            escapeChars.put("&nbsp;", ">");
        }
        StringBuilder sb = new StringBuilder(text.length());
        for (int i = 0; i < text.length(); i++) {
            char c = text.charAt(i);
            if (c != '&') {
                sb.append(c);
                continue;
            }
            boolean hasFound = false;
            for (int j = i + 1; j < i + 6; j++) {
                if (j < text.length() && text.charAt(j) == ';') {
                    String key = text.substring(i, j + 1);
                    if (escapeChars.containsKey(key)) {
                        sb.append(escapeChars.get(key));
                        i = j;
                        hasFound = true;
                        break;
                    }
                }
            }
            if (!hasFound) {
                sb.append(c);
            }
        }
        return sb.toString();
    }

    //https://social.msdn.microsoft.com/Forums/office/en-US/e27aaf16-b900-4654-8210-83c5774a179c/xlsx-numfmtid-predefined-id-14-doesnt-match?forum=oxmlsdk
    static String[] buildInNumFmt = new String[]{
            /*0-4*/"General", "0", "0.00", "#,##0", "#,##0.00",
            /*5-9*/"", "", "", "", "0%",
            /*10-14*/"0.00%", "0.00E+00", "# ?/?", "# ??/??", "mm-dd-yy",
            /*15-19*/"d-mmm-yy", "d-mmm", "mmm-yy", "h:mm AM/PM", "h:mm:ss AM/PM",
            /*20-24*/"h:mm", "h:mm:ss", "m/d/yy h:mm", "", "",
            /*25-29*/"", "", "[$-404]e/m/d", "", "",
            /*30-34*/"m/d/yy", "", "", "", "",
            /*35-39*/"", "[$-404]e/m/d", "#,##0 ;(#,##0)", "#,##0 ;[Red](#,##0)", "#,##0.00;(#,##0.00)",
            /*40-44*/"#,##0.00;[Red](#,##0.00)", "", "", "", "_(\"$\"* #,##0.00_);_(\"$\"* \\(#,##0.00\\);_(\"$\"* \"-\"??_);_(@_)",
            /*45-49*/"mm:ss", "[h]:mm:ss", "mmss.0", "##0.0E+0", "@",
            /*50-54*/"[$-404]e/m/d", "", "", "", "",
            /*55-59*/"", "", "[$-404]e/m/d", "", "t0",
            /*60-64*/"t0.00", "t#,##0", "t#,##0.00", "", "",
            /*65-69*/"", "", "t0%", "t0.00%", "t# ?/?",
            /*70-74*/"t# ??/??", "", "", "", ""
    };

    public static String formatCell(String value, String formatCode) {
        if (formatCode == null || formatCode.length() < 1) {
            return value;
        }
        boolean hasY = formatCode.contains("y"), hasM = formatCode.contains("m"), hasD = formatCode.contains("d"), hasH = formatCode.contains("h"), hasS = formatCode.contains("s"), isDate, isTime;
        isDate = hasY && hasM || hasY && hasD || hasM && hasD;
        isTime = hasH && hasM || hasH && hasS || hasM && hasS;
        String format = isDate && isTime
                ? "yyyy-MM-dd HH:mm:ss"
                : isDate ? hasY ? "yyyy-MM-dd" : "MM-dd"
                : isTime ? hasS ? "HH:mm:ss" : "HH:mm"
                : "";
        if (format.length() > 0) {
            Date date = toDate(value, isTime);
            if (date != null) {
                SimpleDateFormat sdf = new SimpleDateFormat(format);
                sdf.setTimeZone(TimeZone.getTimeZone("UTC+0"));
                return sdf.format(date);
            }
        }
        return value;
    }

    //解析Excel日期格式
    //https://support.microsoft.com/en-us/office/date-systems-in-excel-e7fe7167-48a9-4b96-bb53-5612a800b487
    static Date toDate(String value, boolean isTime) {
        Double dateValue = Utils.toDouble(value);
        if (dateValue == null) {
            return null;
        }
        if (isTime && dateValue < 1) {
            Date tDate = new Date();
            tDate = DateUtils.truncate(tDate, Calendar.DAY_OF_MONTH);
            BigDecimal bigDecimal = BigDecimal.valueOf(dateValue * 24 * 3600 * 1000);
            bigDecimal = bigDecimal.setScale(0, RoundingMode.HALF_UP);
            return DateUtils.addMilliseconds(tDate, bigDecimal.intValue());
        }
        Date tDate = new Date();
        //long localOffset = Calendar.ZONE_OFFSET * 60000;
        long localOffset = 0;
        //系统时区偏移 1900/1/1 到 1970/1/1 的 25569 天
        tDate.setTime((long) ((dateValue - 25569) * 24 * 3600 * 1000 + localOffset));
        return tDate;
    }

    public static class ExcelRange {
        public final ExcelNum start;
        public final ExcelNum end;

        public ExcelRange(ExcelNum start, ExcelNum end) {
            this.start = start;
            this.end = end;
        }
    }

    public static final class ExcelNum implements CharSequence {
        static final int radix = 'Z' - 'A' + 1;
        private String num;
        private String cell;
        private int cellNum;
        private int rowNum;

        public ExcelNum(String num) {
            this.num = PreRequired.requireNotBlank(num).toUpperCase();
            parse();
        }

        void parse() {
            cell = num.replaceAll("[0-9]", "").trim();
            {
                char[] chars = cell.toCharArray();
                ArrayUtils.reverse(chars);
                for (int i = 0; i < chars.length; i++) {
                    cellNum = cellNum + (chars[i] - 'A' + 1) * Double.valueOf(Math.pow(radix, i)).intValue();
                }
            }
            rowNum = Integer.valueOf(num.replaceAll("[A-Z]", "").trim());
        }

        public static String radix(int num) {
            String cellName = "";
            while (num > 0) {
                int modulo = (num - 1) % radix;
                cellName = ((char) ('A' + modulo)) + cellName;
                num = (num - modulo) / 26;
            }
            return cellName;
        }

        public static int cellIndex(String cellName) {
            int cellIndex = 0, j = 0;
            cellName = StringUtils.reverse(cellName);
            for (int i = 0; i < cellName.length(); i++) {
                char c = cellName.charAt(i);
                if (c >= 'A' && c <= 'Z') {
                    cellIndex = cellIndex + (c - 'A' + 1) * Double.valueOf(Math.pow(radix, j)).intValue();
                    j += 1;
                }
            }
            return cellIndex;
        }

        public static int cellIndex2(String cellName) {
            int cellIndex = 0;
            cellName = StringUtils.reverse(cellName);
            for (int i = 0; i < cellName.length(); i++) {
                char c = cellName.charAt(i);
                if (c >= 'A' && c <= 'Z') {
                    cellIndex = cellIndex + (c - 'A' + 1) * Double.valueOf(Math.pow(radix, i)).intValue();
                }
            }
            return cellIndex;
        }

        public ExcelNum nextCell() {
            int nextCellNum = cellNum + 1;
            return new ExcelNum(radix(nextCellNum) + rowNum);
        }

        public ExcelNum nextCell(int count) {
            int nextCellNum = cellNum + count;
            return new ExcelNum(radix(nextCellNum) + rowNum);
        }

        public ExcelNum nextRow() {
            int nextRowNum = rowNum + 1;
            return new ExcelNum(cell + nextRowNum);
        }

        public ExcelNum nextRow(int count) {
            int nextRowNum = rowNum + count;
            return new ExcelNum(cell + nextRowNum);
        }

        public String getNum() {
            return num;
        }

        public String getCell() {
            return cell;
        }

        public int getCellNum() {
            return cellNum;
        }

        public int getRowNum() {
            return rowNum;
        }

        @Override
        public int length() {
            return num.length();
        }

        @Override
        public char charAt(int index) {
            return num.charAt(index);
        }

        @Override
        public CharSequence subSequence(int start, int end) {
            return num.subSequence(start, end);
        }

        @Override
        public String toString() {
            return cell + rowNum;
        }
    }

    public static void main(String[] args) throws Exception {
        ExcelNum b10 = new ExcelNum("B10");
        System.out.println(b10.cellNum);
        System.out.println(b10.rowNum);
        System.out.println(b10);
        System.out.println(b10.nextCell());
        System.out.println(b10.nextCell(3));
        System.out.println(b10.nextRow());
        System.out.println(b10.nextRow(3));
        List<String> list = Utils.toList("123", "1", "11", "10");
        list = list.stream().sorted(Comparator.comparing(s -> Integer.valueOf(s))).collect(Collectors.toList());
        System.out.println(list);
        XmlXlsxParser model = new XmlXlsxParser(new SnowflakeId(0, 0));
        try {
            model.parse(new File("/Users/zhaochen/Downloads/test-202206-2.xlsx"));
            XmlLinkedMap xmlMap = model.sheetToHtml(new LinkedHashMap());
            System.out.println(JSONUtil.toJsonString(xmlMap, true));
            StringBuilder sb = new StringBuilder();
            sb.append("<html><head><title>Excel</title>" +
                    "    <link rel=\"stylesheet\" href=\"./plugins/fontawesome-free/css/all.min.css\">\n" +
                    "    <!-- Ionicons -->\n" +
                    "    <link rel=\"stylesheet\" href=\"./css/ionicons-2.0.1.min.css\">\n" +
                    "    <!-- Tempusdominus Bootstrap 4 -->\n" +
                    "    <link rel=\"stylesheet\" href=\"./plugins/tempusdominus-bootstrap-4/css/tempusdominus-bootstrap-4.min.css\">\n" +
                    "    <!-- iCheck -->\n" +
                    "    <link rel=\"stylesheet\" href=\"./plugins/icheck-bootstrap/icheck-bootstrap.min.css\">\n" +
                    "    <!-- JQVMap -->\n" +
                    "    <link rel=\"stylesheet\" href=\"./plugins/jqvmap/jqvmap.min.css\">\n" +
                    "    <!-- Theme style -->\n" +
                    "    <link rel=\"stylesheet\" href=\"./dist/css/adminlte.min.css\">\n" +
                    "    <!-- overlayScrollbars -->\n" +
                    "    <link rel=\"stylesheet\" href=\"./plugins/overlayScrollbars/css/OverlayScrollbars.min.css\">\n" +
                    "    <!-- Daterange picker -->\n" +
                    "    <link rel=\"stylesheet\" href=\"./plugins/daterangepicker/daterangepicker.css\">\n" +
                    "    <!-- summernote -->\n" +
                    "    <link rel=\"stylesheet\" href=\"./plugins/summernote/summernote-bs4.min.css\">" +
                    "    <link rel=\"stylesheet\" href=\"./css/xlsx.css\">" +
                    "</head><body>");
            for (String name : xmlMap.keySet()) {
                sb.append(((XmlLinkedMap) xmlMap.attr(name)).attr("html").toString());
            }
            sb.append("</body></html>");
            FileUtils.write(new File("/Users/zhaochen/Downloads/docs-project/src/main/resources/static/xlsx-content.html"), sb, "UTF-8");
            System.out.println(model.sharedStrings);
        } finally {
            model.close();
        }
        //In the 1900 date system, dates are calculated by using January 1, 1900, as a starting point.
        // When you enter a date, it is converted into a serial number that represents
        // the number of days elapsed since January 1, 1900.
        // For example, if you enter July 5, 2011, Excel converts the date to the serial number 40729.
        // This is the default date system in Excel for Windows, Excel 2016 for Mac, and Excel for Mac 2011. If you choose to convert the pasted data, Excel adjusts the underlying values, and the pasted dates match the dates that you copied.
        Date date = new SimpleDateFormat("yyyy-MM-dd").parse("1900-01-01");
        Date date2 = toDate("40729", false);
        System.out.println(new SimpleDateFormat("yyyy-MM-dd").format(date2));
    }
}
